Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Chapter 9
Oracle Instance Tuning

This chapter looks at the various areas of the Oracle instance (such as memory usage and I/O) that you can affect and what you can change. To effectively tune the instance, you must know what is affected by your changes. This chapter looks at some specific areas within the instance, describes how to know if you have a problem, and explains how to make a change.


NOTE:  Oracle instance tuning should be done in conjunction with application tuning. Because much of the instance tuning is based on the data access patterns and memory usage of the application, changes to the application may result in the opportunity to retune the instance for greater optimization. A perfectly optimized Oracle instance cannot make up for a poorly tuned application.

In keeping with the philosophy of organized and effective tuning, I recommend that you make changes one at a time and carefully record the results. As deadlines approach, it is not always possible to extend the testing effort by changing only one parameter at a time. In the long run, however, this approach is much more effective. It is always possible that multiple changes cancel each other out, incorrectly indicating that nothing has happened.

Of course, I am not advocating that you start from scratch every time. Once you have tuned several systems, take the initialization parameters you have found to work and use them as a starting point. From there, you are well on your way to tuning this particular Oracle instance.

This chapter looks first at memory usage within the Oracle instance and then looks at I/O utilization. These two areas are probably where most bottlenecks occur and are the areas you can most effectively tune.

In a computer system, the fastest storage component is the CPU cache, followed by the system memory. I/O to disk is thousands of times slower than an access to memory. This fact is the key for why you try to make effective use of memory whenever possible and defer I/Os whenever you can. The majority of the user response time is actually spent waiting for a disk I/O to occur.

By making good use of caches in memory and reducing I/O overhead, you can optimize performance. The goal is to retrieve data from memory whenever you can and to use the CPU for other activities whenever you have to wait for I/Os. This chapter examines ways to optimize the performance of the system by taking advantage of caching and effective use of the system’s CPUs. It first looks at memory, then at I/O, and then at internal Oracle processes.

Tuning Memory

In the Oracle instance, data is stored in two places: in memory and on disk. Memory has by far the best performance but also has the highest cost. Disk, on the other hand, can store vast amounts of data very cost effectively but has very slow performance relative to memory.

Because memory is the better performer, it is desirable to use memory to access data whenever possible. But because of the vast amounts of data usually accessed and the number of users who need this data, there is a lot of contention on this resource. To make most effective use of memory, you must achieve a balance between the memory used for Oracle caching and the memory needed by the users.

Tuning memory in the Oracle instance involves tuning several major areas:

  The OS (to provide these resources)
  Private SQL and PL/SQL areas
  The shared pool
  The redo log buffer
  The buffer cache

Tuning the Operating System

The first step in tuning memory for the Oracle instance is to make sure that there are sufficient resources available in the operating system. You cannot allocate memory to Oracle that doesn’t exist. Giving Oracle additional memory at the expense of causing paging or swapping is in-effective and hurts performance. Chapter 12, “Operating System-Specific Tuning,” looks at OS-specific issues.

The goal of tuning the operating system for Oracle is to provide enough resources so that Oracle can function. The operating system must provide for the following:

  Enough memory for the SGA to fit into main memory. In most operating systems, this involves allocating a special type of memory structure called shared memory. Shared memory is provided by the operating system to allow multiple processes to access the same memory through special system calls. In many operating systems, the shared memory is locked into place and cannot be swapped or paged.
  Enough memory for the user processes to fit into main memory. Remember that each shadow process, or dispatcher, also consumes memory. For user processes, the amount of memory consumed depends on the number of users connected.
  Avoid paging and swapping. Although it is not uncommon for some paging or swapping to occur, if it occurs frequently, you should take steps to reduce it.
  Enough memory for operating system activities. Remember that other OS activities may become active at various times and therefore allocate memory.
  Enough memory to accommodate Oracle operations such as archiving, loading, online backup, and so on.

Chapter 12 looks at some of the operating system-specific features available for monitoring the amount of memory available and how to allocate more memory for Oracle. In the OS itself, the main function of tuning memory is to allocate it for Oracle. As you see in the following section, Oracle can take advantage of this memory in several ways.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.